package com.example.stroebackstage.dao;
import com.example.stroebackstage.entity.Customer;
import com.example.stroebackstage.utils.LinkMySQL;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Objects;

public class CustomersDao {
    //    增
    public  static boolean insertCustomers(Customer customer){
    Connection conn = null;
    PreparedStatement pstmt = null;
    int numb = 0;
    try {
        conn = LinkMySQL.getConnection();
        String sql = "insert into customer values(?,?,?,?,?,?,?)";
        pstmt = Objects.requireNonNull(conn).prepareStatement(sql);
        pstmt.setString(1,customer.getName());
        pstmt.setString(2,customer.getEmail());
        pstmt.setString(3,customer.getPassword());
        pstmt.setString(4,customer.getAddress());
        pstmt.setString(5,customer.getPAC());
        pstmt.setString(6,customer.getPhone());
        pstmt.setString(7,customer.getFax());
        numb = pstmt.executeUpdate();
    }catch (Exception e) {
        e.printStackTrace();
    }finally {
        LinkMySQL.release(pstmt,conn);
    }
    return numb > 0;
}
    //    删
    public  static boolean delCustomers(String email){
        Connection conn = null;
        PreparedStatement pstmt = null;
        int numb = 0;
        try {
            conn = LinkMySQL.getConnection();
            String sql = "delete from customer where email = ?";
            pstmt = Objects.requireNonNull(conn).prepareStatement(sql);
            pstmt.setString(1,email);
            numb = pstmt.executeUpdate();
        }catch (Exception e) {
            e.printStackTrace();
        }finally {
            LinkMySQL.release(pstmt,conn);
        }
        return numb > 0;
    }
    //    改
    public  static boolean updateCustomers(Customer customer){
        Connection conn = null;
        PreparedStatement pstmt = null;
        int numb = 0;
        try {
            conn = LinkMySQL.getConnection();
            String sql = "update customer set name=?, password=?, address=?, PAC=?, phone=?,fax=? where email = ?";
            pstmt = Objects.requireNonNull(conn).prepareStatement(sql);
            pstmt.setString(1,customer.getName());
            pstmt.setString(2,customer.getPassword());
            pstmt.setString(3,customer.getAddress());
            pstmt.setString(4,customer.getPAC());
            pstmt.setString(5,customer.getPhone());
            pstmt.setString(6,customer.getFax());
            pstmt.setString(7,customer.getEmail());
            numb = pstmt.executeUpdate();
        }catch (Exception e) {
            e.printStackTrace();
        }finally {
            LinkMySQL.release(pstmt,conn);
        }
        return numb > 0;
    }
    //    查
    public static ArrayList<Customer> selectCustomer(){
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet res = null;
        Customer customer = null;
        ArrayList<Customer> customers = new ArrayList<>();
        try {
            conn = LinkMySQL.getConnection();
            String sql = "select * from customer";
            pstmt = Objects.requireNonNull(conn).prepareStatement(sql);
            res = pstmt.executeQuery();
            while (res.next()){
                customer = new Customer
                        (res.getString("name"),res.getString("email"),res.getString("password"),res.getString("address"), res.getString("PAC"),res.getString("phone"),res.getString("fax"));
            customers.add(customer);
            }
        }catch (Exception e) {
            e.printStackTrace();
        }finally {
            LinkMySQL.release(res,pstmt,conn);
        }
        return customers;
    }
    //根据名字或模糊查
    public static  ArrayList<Customer> selectCustomer(String s){
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet res = null;
        Customer customer = null;
        ArrayList<Customer> customers = new ArrayList<>();
        try {
            conn = LinkMySQL.getConnection();
            String sql = "select * from customer where name like ? or email like ?";
            pstmt = Objects.requireNonNull(conn).prepareStatement(sql);
            pstmt.setString(1,"%"+s+"%");
            pstmt.setString(2,"%"+s+"%");
            res = pstmt.executeQuery();
            while (res.next()){
                customer = new Customer
                        (res.getString("name"),res.getString("email"),res.getString("password"),res.getString("address"), res.getString("PAC"),res.getString("phone"),res.getString("fax"));
                customers.add(customer);
            }
        }catch (Exception e) {
            e.printStackTrace();
        }finally {
            LinkMySQL.release(res,pstmt,conn);
        }
        return customers;
    }
    //验证顾客邮箱是否已存在
    public static boolean checkEmail(String email){
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet res = null;
        boolean check = false;
        try {
            conn = LinkMySQL.getConnection();
            String sql = "select * from customer where email = ?";
            pstmt = Objects.requireNonNull(conn).prepareStatement(sql);
            pstmt.setString(1,email);
            res = pstmt.executeQuery();
            if(res.next()){
                check = true;
            }
        }catch (Exception e) {
            e.printStackTrace();
        }finally {
            LinkMySQL.release(res,pstmt,conn);
        }
        return check;
    }

}
